﻿using System;
using System.Collections.Generic;
using Oracle.DataAccess.Client;
using Wicresoft.SelfService.DAO;
using System.Data;

namespace Wicresoft.SelfService.DAL
{
    public class WorkListDAL
    {
        #region MyWorkList Count
        public WorkListCounter getWorkListCounter(string userID, string company)
        {
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                WorkListCounter model = new WorkListCounter();
                MyWorkListCounter mymodel = new MyWorkListCounter();
                mymodel.waiting = this.getMyWorkListCounter(oda, userID, 2);
                mymodel.processing = this.getMyWorkListCounter(oda, userID, 3);
                mymodel.finished = this.getMyWorkListCounter(oda, userID, 4);
                model.MyWorkList = mymodel;
                model.WorkList_Draft = this.getDraftCount(oda, userID);
                model.WorkList_Schedule = this.getTodoCount(oda, userID, company);
                model.WorkList_Done = this.getDoneCount(oda, userID);
                return model;
            }
        }

        private int getMyWorkListCounter(OracleDataAccess oda, string userID, int status)
        {
            int count = 0;
            string sql = "select count(*) from servicerecords where accountid = :userID and stauts = :status ";
            OracleParameter[] commandParameters = new OracleParameter[2];
            OracleParameter oracleParameter = new OracleParameter("userID", OracleDbType.NVarchar2, ParameterDirection.Input);
            oracleParameter.Value = userID;
            commandParameters[0] = oracleParameter;
            OracleParameter oracleParameter1 = new OracleParameter("status", OracleDbType.Int32, ParameterDirection.Input);
            oracleParameter1.Value = status;
            commandParameters[1] = oracleParameter1;
            object obj = oda.ExecuteScalar(sql, commandParameters);
            if (obj == null)
            {
                count = 0;
            }
            else
            {
                count = Convert.ToInt32(obj);
            }
            return count;
        }

        private int getDraftCount(OracleDataAccess oda, string userID)
        {
            int count = 0;
            string sql = "select count(*) from servicerecords where accountid = :userID and ( stauts is null or stauts = 0 )  ";
            OracleParameter[] commandParameters = new OracleParameter[1];
            OracleParameter oracleParameter = new OracleParameter("userID", OracleDbType.NVarchar2, ParameterDirection.Input);
            oracleParameter.Value = userID;
            commandParameters[0] = oracleParameter;
            object obj = oda.ExecuteScalar(sql, commandParameters);
            if (obj == null)
            {
                count = 0;
            }
            else
            {
                count = Convert.ToInt32(obj);
            }
            return count;
        }

        private int getTodoCount(OracleDataAccess oda, string userID, string company)
        {
            int count = 0;
            string sql = "select count(*) from servicerecords where step in (select p.id from steps p,approvalrole q,userrolerelationship w where p.approver = q.rolename and q.id = w.roleid and w.accountid=:userID) and stauts=1 and (companyids like :company or step>2) ";

            OracleParameter[] commandParameters = new OracleParameter[2];
            OracleParameter oracleParameter = new OracleParameter("userID", OracleDbType.NVarchar2, ParameterDirection.Input);
            oracleParameter.Value = userID;
            commandParameters[0] = oracleParameter;
            OracleParameter oracleParameter1 = new OracleParameter("company", OracleDbType.NVarchar2, ParameterDirection.Input);
            oracleParameter1.Value = "%" + company + "%";
            commandParameters[1] = oracleParameter1;
            object obj = oda.ExecuteScalar(sql, commandParameters);
            if (obj == null)
            {
                count = 0;
            }
            else
            {
                count = Convert.ToInt32(obj);
            }
            return count;
        }

        private int getDoneCount(OracleDataAccess oda, string userID)
        {
            int count = 0;
            string sql = "select count(distinct servicerequestid) from ServiceRecordsHistory where accountid = :userID ";
            OracleParameter[] commandParameters = new OracleParameter[1];
            OracleParameter oracleParameter = new OracleParameter("userID", OracleDbType.NVarchar2, ParameterDirection.Input);
            oracleParameter.Value = userID;
            commandParameters[0] = oracleParameter;
            object obj = oda.ExecuteScalar(sql, commandParameters);
            if (obj == null)
            {
                count = 0;
            }
            else
            {
                count = Convert.ToInt32(obj);
            }
            return count;
        }
        #endregion

        #region MyWorkList List

        public int getTodoListCount_By_Query(string userID, string company, Nullable<int> serviceTypeID, string requestID, string userName, DateTime? startTime, DateTime? endTime, string description)
        {
            int count = 0;
            List<ServiceDetail> list = new List<ServiceDetail>();
            List<OracleParameter> parmlist = new List<OracleParameter>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            if (serviceTypeID != null && serviceTypeID != 0)
            {
                whereStr = whereStr + " and serviceTypeID =:serviceTypeID ";
                parmlist.Add(new OracleParameter("serviceTypeID", OracleDbType.Int32, serviceTypeID, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(userName))
            {
                whereStr = whereStr + " and accountID in (select accountID from accountcopy where USERNAME like :userName) ";
                parmlist.Add(new OracleParameter("userName", OracleDbType.NVarchar2, "%" + userName + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                
                whereStr += " and createTime >=:startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59,59);
                
                whereStr += " and createTime <=:endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            whereStr = whereStr + " and (companyids like :companyids or step>2)";
            parmlist.Add(new OracleParameter("companyids", OracleDbType.NVarchar2, "%" + company + "%", ParameterDirection.Input));

            whereStr = whereStr + " and step in (select p.id from steps p,approvalrole q,userrolerelationship w where p.approver = q.rolename and q.id = w.roleid and w.accountid=:userID) ";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
           
            sql = "select count(*) from servicerecords where stauts=1 " + whereStr;

            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                var obj = oda.ExecuteScalar(sql, commandParameters);
                if (obj!=null)
                {
                    count = Convert.ToInt32(obj);
                }
            }
            return count;
        }

        public List<ServiceDetail> getTodoList_By_Query(string userID, string company, Nullable<int> serviceTypeID, string requestID, string userName, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi, DateTime? startTime, DateTime? endTime, string description)
        {
            List<ServiceDetail> list = new List<ServiceDetail>();
            List<OracleParameter> parmlist = new List<OracleParameter>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            string orderbySqlStr = string.Empty;
            if (serviceTypeID != null && serviceTypeID != 0)
            {
                whereStr = whereStr + " and serviceTypeID =:serviceTypeID ";
                parmlist.Add(new OracleParameter("serviceTypeID", OracleDbType.Int32, serviceTypeID, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(userName))
            {
                whereStr = whereStr + " and accountID in (select accountID from accountcopy where USERNAME like :userName) ";
                parmlist.Add(new OracleParameter("userName", OracleDbType.NVarchar2, "%" + userName + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                whereStr += " and createTime >=:startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr += " and createTime <=:endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            whereStr = whereStr + " and (companyids like :companyids or step>2)";
            parmlist.Add(new OracleParameter("companyids", OracleDbType.NVarchar2, "%" + company + "%", ParameterDirection.Input));

            whereStr = whereStr + " and step in (select p.id from steps p,approvalrole q,userrolerelationship w where p.approver = q.rolename and q.id = w.roleid and w.accountid=:userID) ";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            if (orderBy != null)
            {
                ITServiceDAL its = new ITServiceDAL();
                orderbySqlStr = its.PrepareSqlOrderByStr(orderBy);
                its.Dispose();
            }
            if (ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = (pi.Value - 1) * ps.Value;
                //int endIndex = (pi.Value + 1) * ps.Value;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    orderbySqlStr = "order by null";
                }
                sql = string.Concat("select * from (select * from (select t.*,row_number() OVER(", orderbySqlStr, ") AS \"row_number\" from servicerecords t where stauts=1", whereStr, ") p where p.\"row_number\" > ", beginIndex, " ) q where rownum <=  ", ps);
            }
            else
            {
                sql = "select * from servicerecords where stauts=1 " + whereStr + " " + orderbySqlStr;
            }
            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                using (OracleDataReader dr = oda.ExecuteReader(sql, commandParameters))
                {
                    list = this.getServiceDetailALL(dr);
                }
            }
            return list;
        }

        public int getDoneListCount_By_Query(string userID, string requestID, string description, Nullable<int> serviceTypeID, Nullable<int> status, string userName, Nullable<DateTime> startTime, Nullable<DateTime> endTime)
        {
            int count = 0;
            List<ServiceDetail> list = new List<ServiceDetail>();
            List<OracleParameter> parmlist = new List<OracleParameter>();
            string sql = string.Empty;
            string whereStr = string.Empty;

            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (serviceTypeID != null && serviceTypeID != 0)
            {
                whereStr = whereStr + " and serviceTypeID = :serviceTypeID ";
                parmlist.Add(new OracleParameter("serviceTypeID", OracleDbType.Int32, serviceTypeID, ParameterDirection.Input));
            }
            if (status != null)
            {
                if (status == 0)//查询草稿
                {
                    //whereStr = whereStr + " and (stauts = 0 or stauts is null) ";
                }
                else
                {
                    whereStr = whereStr + " and stauts = :stauts ";
                    parmlist.Add(new OracleParameter("stauts", OracleDbType.Int32, status, ParameterDirection.Input));
                }
            }
            if (!string.IsNullOrEmpty(userName))
            {
                whereStr = whereStr + " and accountID in (select accountID from accountcopy where userName like :userName) ";
                parmlist.Add(new OracleParameter("userName", OracleDbType.NVarchar2, "%" + userName + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and serviceRequestID in (select serviceRequestID from ServiceRecordsHistory where accountid = :userID)";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));

            sql = "select count(*) from servicerecords where stauts>=1 " + whereStr;

            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                var obj = oda.ExecuteScalar(sql, commandParameters);
                if (obj != null)
                {
                    count = Convert.ToInt32(obj);
                }
            }
            return count;
        }
        public List<ServiceDetail> getDoneList_By_Query(string userID, string requestID, string description, Nullable<int> serviceTypeID, Nullable<int> status, string userName, Nullable<DateTime> startTime, Nullable<DateTime> endTime, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceDetail> list = new List<ServiceDetail>();
            List<OracleParameter> parmlist = new List<OracleParameter>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            string orderbySqlStr = string.Empty;

            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (serviceTypeID != null && serviceTypeID != 0)
            {
                whereStr = whereStr + " and serviceTypeID = :serviceTypeID ";
                parmlist.Add(new OracleParameter("serviceTypeID", OracleDbType.Int32, serviceTypeID, ParameterDirection.Input));
            }
            if (status != null)
            {
                if (status == 0)//查询草稿
                {
                    //whereStr = whereStr + " and (stauts = 0 or stauts is null) ";
                }
                else
                {
                    whereStr = whereStr + " and stauts = :stauts ";
                    parmlist.Add(new OracleParameter("stauts", OracleDbType.Int32, status, ParameterDirection.Input));
                }
            }
            if (!string.IsNullOrEmpty(userName))
            {
                whereStr = whereStr + " and accountID in (select accountID from accountcopy where userName like :userName) ";
                parmlist.Add(new OracleParameter("userName", OracleDbType.NVarchar2, "%" + userName + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and serviceRequestID in (select serviceRequestID from ServiceRecordsHistory where accountid = :userID)";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            if (orderBy != null)
            {
                ITServiceDAL its = new ITServiceDAL();
                orderbySqlStr = its.PrepareSqlOrderByStr(orderBy);
                its.Dispose();
            }
            if (ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = (pi.Value - 1) * ps.Value;
                //int endIndex = (pi.Value + 1) * ps.Value;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    orderbySqlStr = "order by null";
                }

                sql = string.Concat("select * from (select * from (select t.*,row_number() OVER(", orderbySqlStr, ") AS \"row_number\" from servicerecords t where stauts>=1", whereStr, ") p where p.\"row_number\" > ", beginIndex, " ) q where rownum <=  ", ps);

            }
            else
            {
                sql = "select * from servicerecords where stauts>=1 " + whereStr + " " + orderbySqlStr;
            }
            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                using (OracleDataReader dr = oda.ExecuteReader(sql, commandParameters))
                {
                    list = this.getServiceDetailALL(dr);
                }
            }
            return list;
        }
        public int getMyApplyListCount_By_Query(string userID, string requestID, string description, Nullable<int> status, Nullable<DateTime> startTime, Nullable<DateTime> endTime)
        {
            int count = 0;
            List<ServiceDetail> list = new List<ServiceDetail>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            List<OracleParameter> parmlist = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (status != null)
            {
                if (status == 0)//查询草稿
                {
                   // whereStr = whereStr + " and (stauts = 0 or stauts is null) ";
                }
                else
                {
                    whereStr = whereStr + " and stauts = :stauts ";
                    parmlist.Add(new OracleParameter("stauts", OracleDbType.Int32, status, ParameterDirection.Input));
                }
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and accountid = :userID";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            
            sql = "select count(*) from servicerecords where stauts>=1 " + whereStr;

            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                var obj = oda.ExecuteScalar(sql, commandParameters);
                if (obj != null)
                {
                    count = Convert.ToInt32(obj);
                }
            }
            return count;
        }
        public List<ServiceDetail> getMyApplyList_By_Query(string userID, string requestID, string description, Nullable<int> status, Nullable<DateTime> startTime, Nullable<DateTime> endTime, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceDetail> list = new List<ServiceDetail>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            string orderbySqlStr = string.Empty;
            List<OracleParameter> parmlist = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (status != null)
            {
                if (status == 0)//查询草稿
                {
                    //whereStr = whereStr + " and (stauts = 0 or stauts is null) ";
                }
                else
                {
                    whereStr = whereStr + " and stauts = :stauts ";
                    parmlist.Add(new OracleParameter("stauts", OracleDbType.Int32, status, ParameterDirection.Input));
                }
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and accountid = :userID";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            if (orderBy != null)
            {
                ITServiceDAL its = new ITServiceDAL();
                orderbySqlStr = its.PrepareSqlOrderByStr(orderBy);
                its.Dispose();
            }
            if (ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = (pi.Value - 1) * ps.Value;
                //int endIndex = (pi.Value + 1) * ps.Value;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    orderbySqlStr = "order by null";
                }
                sql = string.Concat("select * from (select * from (select t.*,row_number() OVER(", orderbySqlStr, ") AS \"row_number\" from servicerecords t where stauts>=1", whereStr, ") p where p.\"row_number\" > ", beginIndex, " ) q where rownum <=  ", ps);
            }
            else
            {
                sql = "select * from servicerecords where stauts>=1 " + whereStr + " " + orderbySqlStr;
            }
            //sql = "select * from servicerecords where stauts>=1 " + whereStr;
            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                using (OracleDataReader dr = oda.ExecuteReader(sql, commandParameters))
                {
                    list = this.getServiceDetailALL(dr);
                }
            }
            return list;
        }
        public int getDraftListCount_By_Query(string userID, string requestID, string description, Nullable<DateTime> startTime, Nullable<DateTime> endTime)
        {
            int count = 0;
            List<ServiceDetail> list = new List<ServiceDetail>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            List<OracleParameter> parmlist = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and accountid = :userID";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            
            sql = "select count(*) from servicerecords where ( stauts is null or stauts = 0 ) " + whereStr;

            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                var obj = oda.ExecuteScalar(sql, commandParameters);
                if (obj != null)
                {
                    count = Convert.ToInt32(obj);
                }
            }
            return count;
        }
        public List<ServiceDetail> getDraftList_By_Query(string userID, string requestID, string description, Nullable<DateTime> startTime, Nullable<DateTime> endTime, OrderbyParameters[] orderBy, Nullable<int> ps, Nullable<int> pi)
        {
            List<ServiceDetail> list = new List<ServiceDetail>();
            string sql = string.Empty;
            string whereStr = string.Empty;
            string orderbySqlStr = string.Empty;
            List<OracleParameter> parmlist = new List<OracleParameter>();
            if (!string.IsNullOrEmpty(requestID))
            {
                whereStr = whereStr + " and serviceRequestID like :serviceRequestID ";
                parmlist.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, "%" + requestID + "%", ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(description))
            {
                whereStr += " and ( description like :description or BDDescription like :bddescription ) ";
                parmlist.Add(new OracleParameter("description", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
                parmlist.Add(new OracleParameter("bddescription", OracleDbType.NVarchar2, "%" + description + "%", ParameterDirection.Input));
            }
            if (startTime != null)
            {
                whereStr = whereStr + " and createTime >= :startTime ";
                parmlist.Add(new OracleParameter("startTime", OracleDbType.Date, startTime, ParameterDirection.Input));
            }
            if (endTime != null)
            {
                DateTime mEndTime = new DateTime(endTime.Value.Year, endTime.Value.Month, endTime.Value.Day, 23, 59, 59);
                whereStr = whereStr + " and createTime <= :endTime ";
                parmlist.Add(new OracleParameter("endTime", OracleDbType.Date, mEndTime, ParameterDirection.Input));
            }
            whereStr = whereStr + " and accountid = :userID";
            parmlist.Add(new OracleParameter("userID", OracleDbType.NVarchar2, userID, ParameterDirection.Input));
            if (orderBy != null)
            {
                ITServiceDAL its = new ITServiceDAL();
                orderbySqlStr = its.PrepareSqlOrderByStr(orderBy);
                its.Dispose();
            }
            if (ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = (pi.Value - 1) * ps.Value;
                //int endIndex = (pi.Value + 1) * ps.Value;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    orderbySqlStr = "order by null";
                }
                sql = string.Concat("select * from (select * from (select t.*,row_number() OVER(", orderbySqlStr, ") AS \"row_number\" from servicerecords t where ( stauts is null or stauts = 0 )", whereStr, ") p where p.\"row_number\" > ", beginIndex, " ) q where rownum <=  ", ps);
            }
            else
            {
                sql = "select * from servicerecords where ( stauts is null or stauts = 0 ) " + whereStr + " " + orderbySqlStr;
            }
            OracleParameter[] commandParameters = parmlist.ToArray();
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                using (OracleDataReader dr = oda.ExecuteReader(sql, commandParameters))
                {
                    list = this.getServiceDetailALL(dr);
                }
            }
            return list;
        }
        
        private List<ServiceDetail> getServiceDetail(OracleDataReader dr)
        {
            List<ServiceDetail> list = new List<ServiceDetail>();
            while (dr.Read())
            {
                ServiceDetail sd = new ServiceDetail();
                var id = dr["ID"];
                sd.ID = id == DBNull.Value ? default(int) : Convert.ToInt32(id);
                sd.serviceRequestID = Convert.ToString(dr["serviceRequestID"]);
                sd.flowInstanceId = Convert.ToString(dr["flowInstanceId"]);
                sd.accountID = Convert.ToString(dr["accountID"]);
                var serviceTypeID = dr["ServiceTypeID"];
                sd.serviceTypeID = serviceTypeID == DBNull.Value ? default(int) : Convert.ToInt32(serviceTypeID);
                //var breakDownID = dr["BreakDownID"];
                //sd.breakDownID = breakDownID == DBNull.Value ? default(int) : Convert.ToInt32(breakDownID);
                sd.appName = Convert.ToString(dr["AppSystem"]);
                sd.computerName = Convert.ToString(dr["ComputerName"]);
                sd.ipAdress = Convert.ToString(dr["ipAdress"]);
                sd.os = Convert.ToString(dr["os"]);
                sd.mac = Convert.ToString(dr["mac"]);
                sd.browser = Convert.ToString(dr["browser"]);
                string des = Convert.ToString(dr["description"]);
                if (des.Length > 10)
                {
                    des = des.Substring(0, 8) + "..";
                }
                sd.description = des;
                var stauts = dr["stauts"];
                sd.stauts = stauts == DBNull.Value ? default(Int16) : Convert.ToInt32(stauts);
                var stepid = dr["step"];
                sd.stepID = stepid == DBNull.Value ? default(Int16) : Convert.ToInt32(stepid);
                sd.createTime = Convert.ToDateTime(dr["createTime"]);
                var updatetime = dr["updateTime"];
                if (updatetime == DBNull.Value)
                {
                    sd.updateTime = null;
                }
                else
                {
                    sd.updateTime = Convert.ToDateTime(updatetime);
                }
                sd.approveGroup = Convert.ToString(dr["approveGroup"]);
                sd.approveOwner = Convert.ToString(dr["approveOwner"]);
                sd.companyIDs = Convert.ToString(dr["companyIDs"]);
                string bddes = Convert.ToString(dr["BDDescription"]);
                if (bddes.Length > 10)
                {
                    bddes = bddes.Substring(0, 8) + "..";
                }
                sd.BDDescription = bddes;
                sd.BDSummary = Convert.ToString(dr["BDSummary"]);
                sd.breakDownlvl1 = Convert.ToString(dr["breakDownlvl1"]);
                sd.breakDownlvl2 = Convert.ToString(dr["breakDownlvl2"]);
                sd.breakDownlvl3 = Convert.ToString(dr["breakDownlvl3"]);
                sd.IncidentNumber = Convert.ToString(dr["IncidentNumber"]);
                sd.SubmitType = 1;//自服务提交
                list.Add(sd);
            }
            return list;
        }
        private List<ServiceDetail> getServiceDetailALL(OracleDataReader dr)
        {
            List<ServiceDetail> list = this.getServiceDetail(dr);
            ITServiceDAL itdal = new ITServiceDAL();
            foreach (var sd in list)
            {
                UserInfo account = itdal.getUserInfo_By_AccountID(sd.accountID);
                sd.account = account;
                ServiceList serviceType = itdal.getServiceList_By_serviceTypeID(sd.serviceTypeID);
                sd.serviceType = serviceType;
                ApproveSteps step = itdal.getApproveSteps_By_ID(sd.stepID);
                string approveowner = sd.approveOwner;
                //只有状态在审批中-1，等待中-2，处理中-3下，才有当前处理人
                if (0 < sd.stauts && sd.stauts < 4)
                {
                    if (string.IsNullOrEmpty(approveowner))
                    {
                        if (step.stepName != "event_process")
                        {
                            sd.CurrentProcessor = itdal.getCurrentProcessor_By_RequestID(sd.serviceRequestID);
                        }
                        else
                        {
                            sd.CurrentProcessor = "ITSM服务台";
                        }
                    }
                    else
                    {
                        sd.CurrentProcessor = approveowner;
                    }
                }
                sd.step = step;
            }
            itdal.Dispose();
            return list;
        }
        #endregion


        #region  Update ServiceRecords For ITSM
        public int UpdateServiceRecords(string requestID, string status, string approver, string incidentNumber ,out string sql)
        {
            int count = 0;
            string sqlStr = string.Empty;
            string setStr = string.Empty;
            List<OracleParameter> parmList = new List<OracleParameter>();
            bool update = false;
            if (!string.IsNullOrEmpty(status))
            {
                update = true;
                setStr = setStr + " stauts=:status, ";
                parmList.Add(new OracleParameter("status", OracleDbType.Int32, mapStatus(status), ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(approver))
            {
                update = true;
                setStr = setStr + " approveowner=:approver, ";
                parmList.Add(new OracleParameter("approver", OracleDbType.NVarchar2, approver, ParameterDirection.Input));
                
            }
            if (!string.IsNullOrEmpty(incidentNumber))
            {
                update = true;
                setStr = setStr + " incidentNumber=:incidentNumber, ";
                parmList.Add(new OracleParameter("incidentNumber", OracleDbType.NVarchar2, incidentNumber, ParameterDirection.Input));
            }
            if (update)
            {
                setStr = (setStr + " updatetime=:updatetime,").Trim(',');
                parmList.Add(new OracleParameter("updatetime", OracleDbType.Date, DateTime.Now, ParameterDirection.Input));
                sqlStr = "update servicerecords set " + setStr + "  where serviceRequestID=:serviceRequestID";
                
                parmList.Add(new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, requestID, ParameterDirection.Input));
                OracleParameter[] commandParameters = parmList.ToArray();
                using (OracleDataAccess oda = new OracleDataAccess())
                {
                    count =oda.ExecuteCommand(sqlStr, commandParameters);
                }
            }
            sql = "------------------sql-----------" +sqlStr;
            sql = sql + "------------------OracleParameter-----------" ;
            foreach(OracleParameter i in parmList)
            {
                sql = sql + "  Value:" + i.Value + "  ParameterName:" + i.ParameterName + " OracleDbType:" + i.OracleDbType + "          ";

            }
            return count;
        }
        public int UpdateServiceRecordsStatus(string requestID, string status)
        {
            string sqlStr = string.Empty;
            sqlStr = "update servicerecords set stauts=:status, updatetime=:updatetime where serviceRequestID=:serviceRequestID";
            OracleParameter[] commandParameters = new OracleParameter[3];
            commandParameters[0] = new OracleParameter("status", OracleDbType.Int32, mapStatus(status), ParameterDirection.Input);
            commandParameters[1] = new OracleParameter("updatetime", OracleDbType.Date, DateTime.Now, ParameterDirection.Input);
            commandParameters[2] = new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, requestID, ParameterDirection.Input);
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                int count = oda.ExecuteCommand(sqlStr, commandParameters);
                return count;
            }
        }
        public int UpdateServiceRecordsApprover(string requestID, string approver)
        {
            string sqlStr = string.Empty;
            sqlStr = "update servicerecords set approveowner=:approver, updatetime=:updatetime where serviceRequestID=:serviceRequestID";
            OracleParameter[] commandParameters = new OracleParameter[3];
            commandParameters[0] = new OracleParameter("approver", OracleDbType.NVarchar2, approver, ParameterDirection.Input);
            commandParameters[1] = new OracleParameter("updatetime", OracleDbType.Date, DateTime.Now, ParameterDirection.Input);
            commandParameters[2] = new OracleParameter("serviceRequestID", OracleDbType.NVarchar2, requestID, ParameterDirection.Input);
            using (OracleDataAccess oda = new OracleDataAccess())
            {
                int count = oda.ExecuteCommand(sqlStr, commandParameters);
                return count;
            }
        }
        private int mapStatus(string status)
        { 
            switch(status)
            {
                case "Assigned":
                    return 2;
                case "In Progress":
                    return 3;
                case "Pending":
                    return 3;
                case "Resolved":
                    return 3;
                case "Closed":
                    return 4;
                case "Cancelled":
                    return 6;
                default:
                    throw new ArgumentException("状态参数\""+status+"\"错误，无法匹配");
            }
        }
        #endregion
    }
}
